﻿CREATE PROCEDURE [dbo].[state_Repo_MoveByDateRange](
	@StatusID int,
	@Direction int -- -1 back, 1 forward, 0 to nearest by Dir
)AS
SET NOCOUNT ON

DECLARE @DaysInQueue tinyint,@Subject varchar(512)

SELECT  @DaysInQueue = CONVERT(tinyint,StateParameterValue) FROM t_State_Parameters
WHERE (StateID = @StatusID) AND StateParameterID = dbo.fs_StateParamID__GracePeriod()

--DECLARE @T TABLE(ID int,LoanID int,StatusID int,StatusDate datetime,StatusUser varchar(256),AgentUserName	nvarchar(256),IsInRepo	int)

--SELECT *,@DaysInQueue DaysInQueue FROM t_Repossession
--WHERE StatusID = @StatusID AND DATEDIFF(dd,StatusDate,GETDATE())>@DaysInQueue
--ORDER BY StatusDate


UPDATE R WITH(UPDLOCK) SET StatusID = dbo.fs_NextState(StatusID,@Direction)
--OUTPUT inserted.* INTO @T
FROM dbo.t_Repossession R LEFT OUTER JOIN dbo.v_Note_Last NL ON R.LoanID = NL.LoanID
WHERE R.StatusID = @StatusID
	AND dbo.clr_BusinessDaysDiff(R.StatusDate,GETDATE(),1) > @DaysInQueue
	AND dbo.clr_BusinessDaysDiff(ISNULL(NL.NoteDate,'1/1/2000'),GETDATE(),1) > @DaysInQueue

SET @Subject = 'state_Repo_MoveByDateRange: '+CONVERT(varchar,@@ROWCOUNT)+' accounts.'
PRINT @Subject
EXEC admin.s_Mail_System @Subject,'',''

--SELECT * FROM @T ORDER BY StatusDate
--BEGIN TRAN
--EXECUTE AS Login='AFC-HOME\SQLServer'
--DECLARE @StatusID int SET @StatusID = dbo.fs_StateID__Repo__OutForRepo()
--EXEC state_Repo_MoveByDateRange @StatusID,-1
--SELECT * FROM t_Repossession WHERE LoanID = 1001167
--REVERT
--ROLLBACK



